Blog.

While working on some code at my job the other day, I came across an instance where I needed to use the "LIKE" keyword in a query to filter out some records from my return. As anyone who writes code knows, it's a common practice to even a beginner and just the use of it, in and of itself, is nothing worth writing about. However, I discovered a thing or two about it that I found quite irritating and am on the fence about whether it's behavior is correct or bugged. I can assure you that, whatever the case is, it is maddening in the sense that it doesn't seem correct at all. Anyways, on with the story......

"LIKE" is Case-Sensitive

"SELECT * FROM tablename WHERE table_column LIKE '%somevalue%'"

is not the same as

"SELECT * FROM tablename WHERE table_column LIKE '%SomeValue%'"

So, if you use either statement as is and do not use some sort of case function to account for the casing of your requested data, you will certainly get unexpected record sets. This isn't what is my main frustration, but I include it in case anyone wanted to know and just to make myself feel really smart.

So, one way to deal with unexpected casing of characters in your return is to use the lcase() or ucase() functions as so:

"SELECT * FROM tablename WHERE ucase(table_column) LIKE '%SOMEVALUE%'"

or

"SELECT * FROM tablename WHERE lcase(table_column) LIKE '%somevalue%'"

This will force the return into the desired case for comparison and help resolve those pesky issues where the person who entered the data didn't follow your instructions or you just don't know what to expect.

And, remember, the % wildcard matters. In MySQL (and probably other database languages), if you don't include the % symbol, you are searching for an exact match. '%somevalue' will not match 'somevaluehere' but will match 'heresomevalue'. 'somevalue%' will match 'somevaluehere' but not 'heresomevalue'

Now, for the really aggravating part....

In MySQL, NULL is not the same as ''. While ColdFusion (and probably most other programming languages) will evaluate them in the same manner, they are not the same. Why is this aggravating? Because, when passing SQL statements in which you will need to use a LIKE comparator, the NULL field is not included in the comparison. This yields some very unexpected output if you don't know that NULL fields are not included in the 'LIKE' comparison.

Examine the following:

Column_a	Column_b	Column_c
value_a	value_b	value_c
another value a	another value b	another value c
still some other value a	still some other value b	(NULL)

and this statement:

SELECT *
FROM theabovetable
WHERE Column_c NOT LIKE '%value%';

The result set will have no records. The NULL row is ignored for the comparison. To include it in the result, you must test for the NULL condition like so:

SELECT *
FROM theabovetable
WHERE Column_c NOT LIKE '%value%'
AND Column_c = NULL;

That statement will include the NULL row in the returned recordset.

That fact is somewhat confusing to me because it seems to me that NULL is not like 'value', is it not? So, why do I also have to test for a third condition when I really only need to know if a value isn't like the one I want filtered out? Maybe I'm missing something, but if I say "NOT LIKE '%somevalue%', then NULL would qualify.

This quandary has cost me a good bit of time and some scalp material. Hopefully some one else finds this helpful.

If I've missed something or you disagree with something I've said or would like to add to what I've said, shoot me an email.

posted 05/01/2017 in Uncategorized

Tags: MySQL